IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapAnimalsProtocolProcessIDsForApproval]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MapAnimalsProtocolProcessIDsForApproval]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************
* Name:			MapAnimalsProtocolProcessIDsForApproval
* Purpose:		Update Stg_AnimalProtocolSubmission with Submissiont Type and OtherEvent Type IDs
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.MapAnimalsProtocolProcessIDsForApproval
AS
BEGIN


	SET NOCOUNT ON

	-- Map the Initial Reviews to the appropriate Process records		
	UPDATE ProtocolSubmission SET ProtocolProcessID = SS.ProtocolProcessID
	FROM (SELECT P.Id as ProtocolId, PP.Id as ProtocolProcessID FROM ProtocolProcess PP
			inner join ProtocolSubmission Sub
				on Sub.ProtocolId = PP.ProtocolId
			inner join Protocol P 
				on P.Id = PP.ProtocolId 
				and P.Id = Sub.ProtocolId
			WHERE PP.Type = 'ANIMALPPTYPE_IR' 
			AND Sub.FormsID = 'IR' 
			AND P.ModuleType = 'A') SS
	WHERE SS.ProtocolId = ProtocolSubmission.ProtocolId
	AND ProtocolSubmission.FormsID = 'IR'
	AND ProtocolSubmission.ProtocolProcessID is null
	AND ProtocolSubmission.ModuleType = 'A'



SET NOCOUNT OFF

END